package com.waijiaojun.tpo.crawler;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Created by Peter on 2018/2/8.
 */
public class PoiCategoryUtil {
    public static void main(String[] args) {
        String excelPath = "d://托福TPO话题分类.xlsx";
        List<CategoryRefScenarioDto> list =getCateRefScenDto(excelPath);

        for(CategoryRefScenarioDto dto:list){
            System.out.println(dto.toString());
        }
    }

    public static List<CategoryRefScenarioDto> getCateRefScenDto(String excelFilePath) {
        List<CategoryRefScenarioDto> list = new ArrayList<>();
        try (Workbook wb = WorkbookFactory.create(new File(excelFilePath))) {
            Sheet sheet1 = wb.getSheetAt(0);
            long row_n = 0L;
            long col_n = 0L;
            for (Row row : sheet1) {
                System.out.println("row:" + ++row_n);
                if (row_n == 33) {
                    break;
                }
                for (Cell cell : row) {
                    String text = cell.getStringCellValue();
                    if (StringUtils.isBlank(text)) {
                        break;
                    }
                    if (col_n != 0) {
                        String result = extraOrOg(text);
                        if (result == null) {
                            result = getTpoN(text);
                        }
                        CategoryRefScenarioDto dto = new CategoryRefScenarioDto(row_n,result,col_n);
                        list.add(dto);
                        // System.out.print("c" + col_n + ": " + result  + " | ");
                    }
                    col_n++;
                }
                col_n = 0;
                // System.out.println();
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        return list;
    }

    private static String getTpoN(String text) {
        String tpoName = "TPO";
        tpoName += text.split(" ")[0];
        return tpoName;
    }

    private static String extraOrOg(String text) {
        // 按指定模式在字符串查找
        String pattern_extra = "Extra[\\s]*[\\d]+[\\.][\\d]+[\\s]";
        String result = match(text, pattern_extra);

        if (result == null) {
            String pattern_og = "OG[\\s]*[\\d]+[\\.][\\d]+[\\s]";
            result = match(text, pattern_og);
        }
        if (result != null) {
            result = result.replace(" ", "").toUpperCase();
        }
        return result;
    }

    // 正则匹配，有匹配结果返回第一个匹配值，没有匹配结果返回null
    private static String match(String text, String pattern) {
        String result = null;
        // 创建 Pattern 对象
        Pattern r = Pattern.compile(pattern);

        // 现在创建 matcher 对象
        Matcher m = r.matcher(text);
        if (m.find()) {
            result = m.group(0);
            //System.out.println("MATCH Text:" + result + " for:" + text);
        } else {
            //System.out.println("NO MATCH for:" + text);
        }
        return result;
    }
}
